package com.kkb.dao.impl;

import com.kkb.bean.Express;
import com.kkb.bean.RankingExpress;
import com.kkb.dao.BaseExpressDao;
import com.kkb.exception.DuplicateCodeException;
import com.kkb.utils.DateFormatUtil;
import com.kkb.utils.DruidUtil;

import javax.xml.crypto.Data;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class BaseExpressMysql implements BaseExpressDao {
    // 查询 当日全部快递（总数+新增），当日待取件快递（总数+新增）
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size, COUNT(TO_DAYS(intime) = TO_DAYS(NOW()) OR NULL) data1_day ,COUNT(STATUS = 0 OR NULL) data2_size , COUNT(TO_DAYS(intime) = TO_DAYS(NOW()) AND STATUS = 0 OR NULL) data2_day FROM express";
    // 用于查询所有快递
    private static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    // 用于 分页 查询所有快递
    private static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    // 通过取件码查询快递信息
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE =?";
    // 通过快递单号查询快递信息
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER =?";
    // 通过手机号查询
    private static final String SQL_FIND_BY_PHONE_NUMBER = "SELECT * FROM EXPRESS WHERE USERPHONE =?";
    // 通过录入人电话查询快递
    private static final String SQL_FIND_BY_SYS_PHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE =? ORDER BY INTIME DESC";
    // 录入快递
    private static final String SQL_INSERT = "INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,?,?,?)";
    // 更新快递信息
    private static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
    // 更新快递状态--取件
    private static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1, OUTTIME=NOW(),CODE=NULL WHERE CODE=?";
    // 删除快递
    private static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";

    // 通过用户手机号+未取件的快递
    private static final String SQL_FIND_BY_PHONE_AND_STATUS0 = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND STATUS=?";
    // 查找快递单号是否存在
    private static final String SQL_IS_EXIST_NUMBER = "SELECT ID FROM EXPRESS WHERE NUMBER = ?";
    //
    private static final String SQL_RANKING_BY_ALL = "SELECT USERNAME,COUNT(*) count FROM EXPRESS GROUP BY USERNAME ORDER BY COUNT(*) DESC ";
    //
    private static final String SQL_RANKING_BY_YEAR = "SELECT USERNAME,COUNT(*) count FROM EXPRESS WHERE YEAR(INTIME)=YEAR(NOW()) GROUP BY USERNAME ORDER BY COUNT(*) DESC ";
    //
    private static final String SQL_RANKING_BY_MONTH = "SELECT USERNAME,COUNT(*) count FROM EXPRESS WHERE MONTH(INTIME)=MONTH(NOW()) GROUP BY USERNAME ORDER BY COUNT(*) DESC";



    /**
     * 用于查询数据库中的 当日全部快递（总数+新增），当日待取件快递（总数+新增）
     *
     * @return [{size：总数，day：新增},{size:总数，day新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<Map<String, Integer>> list = new ArrayList<>();
        try {
            pst = connection.prepareStatement(SQL_CONSOLE);
            rs = pst.executeQuery();
            if (rs.next()){
                int data1_size = rs.getInt("data1_size");
                int data1_day = rs.getInt("data1_day");
                HashMap<String, Integer> data1 = new HashMap<>();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                int data2_size = rs.getInt("data2_size");
                int data2_day = rs.getInt("data2_day");
                HashMap<String, Integer> data2 = new HashMap<>();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                list.add(data1);
                list.add(data2);
            }
            return list;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return null;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit      标志是否分页，true表示分页，false表示查询所有快递
     * @param offset     SQL语句的其实索引
     * @param pageNumber 页的数据条数
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Express> data = new ArrayList<>();
        try {
            if (limit){
                // 分页查询
                pst = connection.prepareStatement(SQL_FIND_LIMIT);
                // 传递参数
                pst.setInt(1,offset);
                pst.setInt(2,pageNumber);
            } else {
                // 查询全部
                pst = connection.prepareStatement(SQL_FIND_ALL);
            }
            rs = pst.executeQuery();
            // 封装数据
            while(rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String userPhone = rs.getString("userphone");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("intime");
                Timestamp outTime = rs.getTimestamp("outtime");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return null;
    }

    /**
     * 根据快递单号，查询快递信息
     *
     * @param number 单号
     * @return 查询到的快递信息，取件码不存在是返回null
     */
    @Override
    public Express findByNumber(String number) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            pst.setString(1,number);
            rs = pst.executeQuery();
            Express e = null;
            if (rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String userPhone = rs.getString("userphone");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("intime");
                Timestamp outTime = rs.getTimestamp("outtime");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysPhone");
                e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
            }
            return e;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return null;
    }

    /**
     * 根据取件码，查询快递信息
     *
     * @param code 取件码
     * @return 查询到的快递信息，取件码不存在是返回null
     */
    @Override
    public Express findByCode(String code) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = connection.prepareStatement(SQL_FIND_BY_CODE);
            pst.setString(1,code);
            rs = pst.executeQuery();
            Express e = null;
            if (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String userPhone = rs.getString("userphone");
                String company = rs.getString("company");
                Timestamp inTime = rs.getTimestamp("intime");
                Timestamp outTime = rs.getTimestamp("outtime");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysPhone");
                e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
            }
            return e;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return null;
    }

    /**
     * 根据手机号，查询快递信息
     *
     * @param phoneNumber 手机号
     * @return 查询到的一些快递信息，手机号不存在是返回null
     */
    @Override
    public List<Express> findByPhoneNumber(String phoneNumber) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = connection.prepareStatement(SQL_FIND_BY_PHONE_NUMBER);
            pst.setString(1,phoneNumber);
            rs = pst.executeQuery();
            ArrayList<Express> data = new ArrayList<>();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String code = rs.getString("code");
                String company = rs.getString("company");
                Timestamp inTime = rs.getTimestamp("intime");
                Timestamp outTime = rs.getTimestamp("outtime");
                int status = rs.getInt("status");
                String sysPhone = rs.getString("sysPhone");
                Express e = new Express(id,number,username,phoneNumber,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return null;
    }

    /**
     * 根据录入人的手机号，查询录入的所有快递信息
     *
     * @param sysPhone 手机号
     * @return 查询快递信息列表
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = connection.prepareStatement(SQL_FIND_BY_SYS_PHONE);
            pst.setString(1,sysPhone);
            rs = pst.executeQuery();
            ArrayList<Express> data = new ArrayList<>();
            while (rs.next()){
                int id = rs.getInt("id");
                String number = rs.getString("number");
                String username = rs.getString("username");
                String code = rs.getString("code");
                String userPhone = rs.getString("userphone");
                String company = rs.getString("company");
                Timestamp inTime = rs.getTimestamp("intime");
                Timestamp outTime = rs.getTimestamp("outtime");
                int status = rs.getInt("status");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return null;
    }

    /**
     * 快读的录入
     *
     * @param e 要录入的快递对象
     * @return 录入的结果，true 表示成功，false 表示失败
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = connection.prepareStatement(SQL_INSERT);
            pst.setString(1,e.getNumber());
            pst.setString(2,e.getUsername());
            pst.setString(3,e.getUserPhone());
            pst.setString(4,e.getCompany());

            pst.setString(5, e.getCode());
            // 当前时间 作为录入时间
            pst.setString(6, DateFormatUtil.format(new Date()));
            pst.setInt(7,0);
            pst.setString(8,e.getSysPhone());

            return pst.executeUpdate() > 0 ? true:false;

        } catch (SQLException exception) {
            if (exception.getMessage().endsWith("for key 'express.code'")){
                // 快递取件码重复
                DuplicateCodeException codeException = new DuplicateCodeException(exception.getMessage());
                throw codeException;
            } else {
                exception.printStackTrace();
            }
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return false;
    }

    /**
     * 快读的录入
     *
     * @param id         要修改的快递id
     * @param newExpress 新的快递对象（number，company，username，userPhone）
     * @return 录入的结果，true 表示成功，false 表示失败
     */
    @Override
    public boolean update(int id, Express newExpress) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        int result = 0;
        try {
            pst = connection.prepareStatement(SQL_UPDATE);
            pst.setString(1,newExpress.getNumber());
            pst.setString(2,newExpress.getUsername());
            pst.setString(3,newExpress.getCompany());
            pst.setInt(4,newExpress.getStatus());
            pst.setInt(5,id);
            result = pst.executeUpdate();
            if (result>0) return true;
            else return false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return false;
    }

    /**
     * 修改快递的状态为1 ，表示快递被取出
     *
     * @param code 取件码
     * @return 修改的结果，true 表示成功，false 表示失败
     */
    @Override
    public boolean updateStatus(String code) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = connection.prepareStatement(SQL_UPDATE_STATUS);
            pst.setString(1,code);
            return pst.executeUpdate()>0 ? true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return false;
    }

    /**
     * 根据id，删除单个快递
     *
     * @param id 要删除快递的id
     * @return 删除结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(int id) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = connection.prepareStatement(SQL_DELETE);
            pst.setInt(1,id);
            return pst.executeUpdate()>0 ? true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return false;
    }

    /**
     * 查询用户未取件的快递列表
     *
     * @return 用户未取件的快递列表
     */
    @Override
    public List<Express> findByPhoneAndStatus(String phone,int status) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Express> list = new ArrayList<>();
        try {
            pst = connection.prepareStatement(SQL_FIND_BY_PHONE_AND_STATUS0);
            pst.setString(1,phone);
            pst.setInt(2,status);
            rs = pst.executeQuery();

            while (rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String number = rs.getString("number");
                String company = rs.getString("company");
                String code = rs.getString("code");
                Timestamp inTime = rs.getTimestamp("intime");
                Timestamp outTime = rs.getTimestamp("outtime");
                String sysPhone = rs.getString("sysPhone");
                Express e = new Express(id,number,username,phone,company,code,inTime,outTime,status,sysPhone);
                list.add(e);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,rs);
        }
        return list;
    }

    /**
     * 判断 快递单号是否存在
     *
     * @param number 单号
     * @return 存在返回true ，不存在返false
     */
    @Override
    public boolean isOneNumber(String number) {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = connection.prepareStatement(SQL_IS_EXIST_NUMBER);
            pst.setString(1,number);
            rs = pst.executeQuery();
            if (rs.next()) return true;
            else return  false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return false;
    }

    /**
     * 根据 用户的快递数量来排行总
     *
     * @return 保存排行榜list
     */
    @Override
    public List<RankingExpress> rankingByAll() {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<RankingExpress> list = new ArrayList<>();
        try {
            pst = connection.prepareStatement(SQL_RANKING_BY_ALL);
            rs = pst.executeQuery();
            while (rs.next()) {
                String username = rs.getString("username");
                int count = rs.getInt("count");
                RankingExpress rankingExpress = new RankingExpress(null, username, count);
                list.add(rankingExpress);
            }
            return list;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return list;
    }

    /**
     * 根据 用户的快递数量来排行按年
     *
     * @return 保存排行榜list
     */
    @Override
    public List<RankingExpress> rankingByYear() {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<RankingExpress> list = new ArrayList<>();
        try {
            pst = connection.prepareStatement(SQL_RANKING_BY_YEAR);
            rs = pst.executeQuery();
            while (rs.next()) {
                String username = rs.getString("username");
                int count = rs.getInt("count");
                RankingExpress rankingExpress = new RankingExpress(null, username, count);
                list.add(rankingExpress);
            }
            return list;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return list;
    }

    /**
     * 根据 用户的快递数量来排行按月
     *
     * @return 保存排行榜list
     */
    @Override
    public List<RankingExpress> rankingByMonth() {
        //1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<RankingExpress> list = new ArrayList<>();
        try {
            pst = connection.prepareStatement(SQL_RANKING_BY_MONTH);
            rs = pst.executeQuery();
            while (rs.next()) {
                String username = rs.getString("username");
                int count = rs.getInt("count");
                RankingExpress rankingExpress = new RankingExpress(null, username, count);
                list.add(rankingExpress);
            }
            return list;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,pst,null);
        }
        return list;
    }


}
